package gx.dao.impl.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.jdbc.ReturningWork;
import org.springframework.stereotype.Repository;

import gx.dao.ProductDao;
import gx.entity.Product;
import gx.entity.common.Page;

@Repository
public class ProductDaoImpl extends BaseDaoImpl<Product> implements ProductDao{
//	public List<Product> findByName(String name){
//		return find("from Product where name like?","%" + name + "%");
//	}

	@Override
	public Page<Product> findByName(int pageSize, int number, String values) {
	
		return find(pageSize,number,"from Product where name like ?","%"+values+"%");
		
	}

	@Override
	public List<Product> findProducts(Integer category_id) {
		return find("from Product where cate_id=?",category_id);
	}

	@Override
	public Map<String, Long> totalInventoryByCategory(final int year) {
		// 入库 status =2
				final String sql = "select pr.name ,sum(pi.num) "
						+ "n from t_purchase_item pi join  "
						+ "t_purchase  p on pi.purc_id = p.id "
						+ "join t_product pr on pi.prod_id = pr.id "
						+ "where  p.status = 2 and year(p.pur_date)= ? "
						+ "group  by  pr.id";

				Map<String, Long> map = getSession().doReturningWork(
						new ReturningWork<Map<String, Long>>() {
							@Override
							public Map<String, Long> execute(Connection conn)
									throws SQLException {
								Map<String, Long> mps = new LinkedHashMap<String, Long>();
								PreparedStatement ps = (PreparedStatement) conn
										.prepareStatement(sql);
								ps.setInt(1, year);
								ResultSet rs = ps.executeQuery();
								while (rs.next()) {
									String name = rs.getString(1);
									Long num = rs.getLong(2);
									mps.put(name, num);
								}
								return mps;
							}
						});

				return map;
			}
	

	@Override
	public Map<String, Long> totalInventory(final int year) {

		// 入库 status =2
		final String sql = "select pr.name ,sum(pi.num) "
				+ "n from t_purchase_item pi join  "
				+ "t_purchase  p on pi.purc_id = p.id "
				+ "join t_product pr on pi.prod_id = pr.id "
				+ "where  p.status = 2 and year(p.pur_date)= ? "
				+ "group  by  pr.name";

		Map<String, Long> map = getSession().doReturningWork(
				new ReturningWork<Map<String, Long>>() {
					@Override
					public Map<String, Long> execute(Connection conn)
							throws SQLException {
						Map<String, Long> mps = new LinkedHashMap<String, Long>();
						PreparedStatement ps = (PreparedStatement) conn
								.prepareStatement(sql);
						ps.setInt(1, year);
						ResultSet rs = ps.executeQuery();
						while (rs.next()) {
							String month = rs.getString(1);
							Long num = rs.getLong(2);
							mps.put(month, num);
						}
						return mps;
					}
				});

		return map;
	}

	@Override
	public Page<Product> findByDate(int pageSize, int number, Date parse) {
		
		return find(pageSize,number,"from Product where expir_date <= ?",parse);
		//'2012-12-15'
		//return find(pageSize,number,"from Product where expir_date <= '2012-12-15');
	}
//				@Override
//				public Page<Product> findByDate(int pageSize, int number) {
//					
//				//	return find(pageSize,number,"from Product where expir_date <= ?",parse);
//					//'2012-12-15'
//					return find(pageSize,number,"from Product where expir_date <= '2012-12-15');
//				}

//				@Override
//				public Page<Product> findByDate(int pageSize, int number, Date parse) {
//					// TODO Auto-generated method stub
//					return null;
//				}
}
